package com.feidian.helper.provider;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;

import android.content.Context;
import android.content.res.XmlResourceParser;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.feidian.helper.R;
import com.feidian.helper.provider.ZkgjProvider.Category;
import com.feidian.helper.provider.ZkgjProvider.College;
import com.feidian.helper.provider.ZkgjProvider.CollegeProfession;
import com.feidian.helper.provider.ZkgjProvider.DegreeType;
import com.feidian.helper.provider.ZkgjProvider.Profession;

public class ZkgjDatabaseHelper extends SQLiteOpenHelper {

	private static final String TAG = "ZkgjDatabaseHelper";

	private static final String DATABASE_NAME = "zkgj.db";

	private static final int DATABASE_VERSION = 10;

	private Context mContext;

	public ZkgjDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(" DROP TABLE IF EXISTS " + DegreeType.TABLE_NAME);
		db.execSQL(" DROP TABLE IF EXISTS " + Category.TABLE_NAME);
		db.execSQL(" DROP TABLE IF EXISTS " + College.TABLE_NAME);
		db.execSQL(" DROP TABLE IF EXISTS " + Profession.TABLE_NAME);
		db.execSQL(" DROP TABLE IF EXISTS " + CollegeProfession.TABLE_NAME);
		db.execSQL("CREATE TABLE " + DegreeType.TABLE_NAME + "("
				+ DegreeType._ID + " INTEGER PRIMARY KEY," + DegreeType.NAME
				+ " TEXT," + DegreeType.TYPE + " TEXT" + ");");
		db.execSQL(SQLConstants.SQL_CREATE_TABLE_CATEGORIES);
		db.execSQL(SQLConstants.SQL_CREATE_TABLE_COLLEGES);
		db.execSQL(SQLConstants.SQL_CREATE_TABLE_PROFESSIONS);
		db.execSQL(SQLConstants.SQL_CREATE_TABLE_COLLEGE_PROFESSIONS);
		db.execSQL(SQLConstants.SQL_COLLEGE_PROFESSION_VIEW);
		
		db.execSQL("CREATE TABLE IF NOT EXISTS filedownlog (id integer primary key autoincrement, downpath varchar(100), threadid INTEGER, downlength INTEGER)");
		
		loadDegreeTypes(db);
		loadCategories(db);
		loadColleges(db);
		loadProfessions(db);
		loadCollegeProfessions(db);

		Log.w(TAG, "ZkgjProvider finished onCreate!!!");
	}

	private void loadDegreeTypes(SQLiteDatabase db) {
		Log.w(TAG, "start loadDegreeTypes!!!");
		List<DegreetypeBean> lists = new ArrayList<DegreetypeBean>();
		XmlResourceParser parser = mContext.getResources().getXml(
				R.xml.degreetypes);

		final int depth = parser.getDepth();
		try {
			int eventType;
			while (((eventType = parser.next()) != XmlPullParser.END_TAG || parser
					.getDepth() > depth)
					&& eventType != XmlPullParser.END_DOCUMENT) {
				if (eventType != XmlPullParser.START_TAG) {
					continue;
				}
				String tag = parser.getName();
				if ("degreetype".equals(tag)) {
					String name = parser.getAttributeValue(null, "name");
					String type = parser.getAttributeValue(null, "type");

					if (name != null && type != null) {
						DegreetypeBean degree = new DegreetypeBean();
						degree.name = name;
						degree.type = type;
						lists.add(degree);
					}
				}

			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} finally {
			Log.w(TAG, "parse xml degreetypes finished!");
		}

		SQLiteStatement stmt = null;
		try {
			stmt = db
					.compileStatement(SQLConstants.SQL_INSERT_TABLE_DEGREE_TYPES);
			for (DegreetypeBean item : lists) {
				stmt.bindString(1, item.type);
				stmt.bindString(2, item.name);
				stmt.executeInsert();
			}
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			Log.w(TAG, "INSERT table degreetype finished!");
		}

		Log.w(TAG, "end loadDegreeTypes!!!");
	}

	private void loadCategories(SQLiteDatabase db) {
		Log.w(TAG, "start loadCategories!!!");
		List<CategoryBean> lists = new ArrayList<CategoryBean>();
		// first parse xml
		XmlResourceParser parser = mContext.getResources().getXml(
				R.xml.categories);

		final int depth = parser.getDepth();
		try {
			int eventType;
			while (((eventType = parser.next()) != XmlPullParser.END_TAG || parser
					.getDepth() > depth)
					&& eventType != XmlPullParser.END_DOCUMENT) {
				if (eventType != XmlPullParser.START_TAG) {
					continue;
				}
				String tag = parser.getName();
				if ("category".equals(tag)) {
					String name = parser.getAttributeValue(null, "name");
					String code = parser.getAttributeValue(null, "code");

					if (name != null && code != null) {
						CategoryBean category = new CategoryBean();
						category.name = name;
						category.code = code;
						lists.add(category);
					}
				}

			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} finally {
			Log.w(TAG, "parse xml categories finished!");
		}

		// second, insert into the table

		SQLiteStatement stmt = null;
		try {
			stmt = db
					.compileStatement(SQLConstants.SQL_INSERT_TABLE_CATEGORIES);
			for (CategoryBean item : lists) {
				stmt.bindString(1, item.code);
				stmt.bindString(2, item.name);
				stmt.executeInsert();
			}
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			Log.w(TAG, "INSERT table categories finished!");
		}
		Log.w(TAG, "end loadCategories!!!");
	}

	private void loadColleges(SQLiteDatabase db) {
		Log.w(TAG, "start loadColleges!!!");
		List<CollegeBean> lists = new ArrayList<CollegeBean>();
		// first parse xml
		XmlResourceParser parser = mContext.getResources().getXml(
				R.xml.colleges);

		final int depth = parser.getDepth();
		try {
			int eventType;
			while (((eventType = parser.next()) != XmlPullParser.END_TAG || parser
					.getDepth() > depth)
					&& eventType != XmlPullParser.END_DOCUMENT) {
				if (eventType != XmlPullParser.START_TAG) {
					continue;
				}
				String tag = parser.getName();
				if ("college".equals(tag)) {
					String name = parser.getAttributeValue(null, "name");
					String code = parser.getAttributeValue(null, "code");

					if (name != null && code != null) {
						CollegeBean college = new CollegeBean();
						college.name = name;
						college.code = code;
						lists.add(college);
					}
				}

			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} finally {
			Log.w(TAG, "parse xml colleges finished!");
		}

		// second, insert into the table

		SQLiteStatement stmt = null;
		try {
			stmt = db.compileStatement(SQLConstants.SQL_INSERT_TABLE_COLLEGES);
			for (CollegeBean item : lists) {
				stmt.bindString(1, item.code);
				stmt.bindString(2, item.name);
				stmt.executeInsert();
			}
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			Log.w(TAG, "INSERT table college finished!");
		}

		Log.w(TAG, "end loadColleges!!!");
	}

	private void loadProfessions(SQLiteDatabase db) {
		Log.w(TAG, "start loadProfessions!!!");
		List<ProfessionBean> lists = new ArrayList<ProfessionBean>();
		// first parse xml
		XmlResourceParser parser = mContext.getResources().getXml(
				R.xml.professions);

		final int depth = parser.getDepth();
		try {
			int eventType;
			while (((eventType = parser.next()) != XmlPullParser.END_TAG || parser
					.getDepth() > depth)
					&& eventType != XmlPullParser.END_DOCUMENT) {
				if (eventType != XmlPullParser.START_TAG) {
					continue;
				}
				String tag = parser.getName();
				if ("profession".equals(tag)) {
					String name = parser.getAttributeValue(null, "name");
					String category = parser
							.getAttributeValue(null, "category");
					String code = parser.getAttributeValue(null, "code");
					String degreeType = parser.getAttributeValue(null,
							"degreeType");

					if (name != null && code != null) {
						ProfessionBean degree = new ProfessionBean();
						degree.name = name;
						degree.category = category;
						degree.code = code;
						degree.degreeType = degreeType;
						lists.add(degree);
					}
				}

			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} finally {
			Log.w(TAG, "parse xml professions finished!");
		}

		// second, insert into the table

		SQLiteStatement stmt = null;
		try {
			stmt = db
					.compileStatement(SQLConstants.SQL_INSERT_TABLE_PROFESSIONS);
			for (ProfessionBean item : lists) {
				stmt.bindString(1, item.code);
				stmt.bindString(2, item.name);
				stmt.bindString(3, item.category);
				stmt.bindString(4, item.degreeType);
				stmt.executeInsert();
			}
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			Log.w(TAG, "INSERT table professions finished!");
		}
		Log.w(TAG, "end loadProfessions!!!");
	}

	private void loadCollegeProfessions(SQLiteDatabase db) {
		Log.w(TAG, "start loadCollegeProfessions!!!");
		List<CollegeProfessionBean> lists = new ArrayList<CollegeProfessionBean>();
		// first parse xml
		XmlResourceParser parser = mContext.getResources().getXml(
				R.xml.college_professions);

		final int depth = parser.getDepth();
		try {
			int eventType;
			while (((eventType = parser.next()) != XmlPullParser.END_TAG || parser
					.getDepth() > depth)
					&& eventType != XmlPullParser.END_DOCUMENT) {
				if (eventType != XmlPullParser.START_TAG) {
					continue;
				}
				String tag = parser.getName();
				if ("college_profession".equals(tag)) {
					String college = parser.getAttributeValue(null, "college");
					String profession = parser.getAttributeValue(null,
							"profession");

					if (college != null && profession != null) {
						CollegeProfessionBean degree = new CollegeProfessionBean();
						degree.college = college;
						degree.profession = profession;
						lists.add(degree);
					}
				}

			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (XmlPullParserException e) {
			e.printStackTrace();
		} finally {
			Log.w(TAG, "parse xml college_professions finished!");
		}

		// second, insert into the table

		SQLiteStatement stmt = null;
		try {
			stmt = db
					.compileStatement(SQLConstants.SQL_INSERT_TABLE_COLLEGE_PROFESSIONS);
			for (CollegeProfessionBean item : lists) {
				stmt.bindString(1, item.college);
				stmt.bindString(2, item.profession);
				stmt.executeInsert();
			}
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			Log.w(TAG, "INSERT table college_professions finished!");
		}
		Log.w(TAG, "end loadCollegeProfessions!!!");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {
		Log.w(TAG, "Upgrading zkgj database from version " + oldVersion
				+ " to " + currentVersion);
		db.execSQL("DROP TABLE IF EXISTS filedownlog");
		onCreate(db);

	}

	private static final class DegreetypeBean {
		public String name;
		public String type;
	}

	private static final class CategoryBean {
		public String name;
		public String code;
	}

	private static final class CollegeBean {
		public String name;
		public String code;
	}

	private static final class ProfessionBean {
		public String name;
		public String code;
		public String degreeType;
		public String category;
	}

	private static final class CollegeProfessionBean {
		public String college;
		public String profession;
	}

}
